/**
 * WhereConditionHelper.java
 * Product:BaseLayer
 * Version:1.0
 * Copyright 2009 by DNE
 * All Rights Reserved.
 */
package com.site.util.base.dao;

import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.site.util.DateUtils;
import com.site.util.StringUtils;


/**
 * HQL生成工具
 */
public class HQLHelper {
	private String entityName;
	private String alias="a";//默认别名是a
	private StringBuffer whereCondition=new StringBuffer("");
	
	/**
	 *  如果没有指定查询别名，则默认使用a
	 * @param entityName 实体名
	 */
	public HQLHelper(String entityName) {
		this.entityName=entityName;
	}
	
	/**
	 * 
	 * @param entityName 实体名
	 * @param alias 查询实体别名
	 */
	public HQLHelper(String entityName,String alias) {
		this.entityName=entityName;
		this.alias=alias;
	}
	
	
	
	
	/**
	 * 等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addEqual(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" = '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" = "+value);
		}
		return this;
	}

	
	/**
	 * 等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addEqual(String property,Object[] values){
		if(values == null || StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if (values.length > 1) {
			whereCondition.append(" ( ");
		}
		for (int i = 0; i < values.length; i++) {
			Object value = values[i];
			if (i > 0) {
				whereCondition.append(" or ");
			}
			if(value instanceof String){
				whereCondition.append(alias+"."+property+" = '"+value.toString().replace("'", "''")+"'");
			}else{
				whereCondition.append(alias+"."+property+" = "+value);
			}
		}
		if (values.length > 1) {
			whereCondition.append(" ) ");
		}
		return this;
	}
	
	/**
	 * oracle日期小于等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addOracleDateLessEqualThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		Date date=null;
		if(value instanceof Date){
			date=DateUtils.changeDays((Date)value, 1);
		}
		else
		{
			date=DateUtils.changeDays((String)value, 1);
		}
		whereCondition.append(alias+"."+property+" <= "+oracleToDate(date));
		return this;
	}
	
	/**
	 * oracle日期小于等于查询(不将天数加1)
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addOracleDateLessEqualThan2(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof Date){
			whereCondition.append(alias+"."+property+" <= "+oracleToDate((Date)value));
		}
		else
		{
			whereCondition.append(alias+"."+property+" <= "+oracleToDate((String)value));
		}
		
		return this;
	}
	
	/**
	 * oracle日期大于等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addOracleDateGreatEqualThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof Date){
			whereCondition.append(alias+"."+property+" >= "+oracleToDate((Date)value));
		}
		else
		{
			whereCondition.append(alias+"."+property+" >= "+oracleToDate((String)value));
		}

		return this;
	}
	
	/**
	 * 等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper orEqual(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" or ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" = '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" = "+value);
		}
		return this;
	}
	
	/**
	 * 等于查询(在属性前面不自动添加别名)
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addEqualNotAppendAlias(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(property+" = '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(property+" = "+value);
		}
		return this;
	}
	
	/**
	 * 不等于查询
	 * @param property 属性
	 * @param value 属性值(Double,String,Integer,Long,Float)
	 * @return 返回当前对象
	 */
	public HQLHelper addNotEqual(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" != '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" !="+value);
		}
		return this;
	}
	
	/**
	 * like模糊查询
	 * @param property
	 * @param value
	 * @return
	 */
	public HQLHelper addLike(String property,String value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		whereCondition.append(alias+"."+property+" like '%"+value.toString().replace("'", "''")+"%'");
		return this;
	}
	
	/**
	 * 查询以指定字符串结尾
	 * @param property
	 * @param value
	 * @return
	 */
	public HQLHelper addEndWith(String property,String value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		whereCondition.append(alias+"."+property+" like '%"+value.toString().replace("'", "''")+"'");
		return this;
	}
	
	/**
	 * 查询以指定字符串开始
	 * @param property
	 * @param value
	 * @return
	 */
	public HQLHelper addStartWith(String property,String value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		whereCondition.append(alias+"."+property+" like '"+value.toString().replace("'", "''")+"%'");
		return this;
	}
	
	/**
	 * IN 查询 
	 * @param property
	 * @param list 集合
	 * @return
	 */
	public HQLHelper addIn(String property,List<String> list){
		if(list==null||list.size()==0){
			return this;
		}
		StringBuffer sb=new StringBuffer("");
		for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
			
			Object obj = iterator.next();
			if(obj==null) {
				continue;
			}
			if(!"".equals(sb.toString())){
				sb.append(",");
			}
			if(obj instanceof String){
				//sb.append("'"+obj.toString().replace("'", "''")+"'");
				sb.append("'"+obj.toString()+"'");
			}else{
				sb.append(obj);
			}
			
		}
		
		if("".equals(sb.toString())){
			return this;
		}
		
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		
		//whereCondition.append(alias+"."+property+" in ("+sb.toString().replace("'", "''")+")");
		whereCondition.append(alias+"."+property+" in ("+sb.toString()+")");
		
		return this;
	}
	
	/**
	 * IN 查询 
	 * @param property
	 * @param Object[] 对象数组
	 * @return
	 */
	public HQLHelper addIn(String property,Object[] array){
		if(array==null||array.length==0){
			return this;
		}
		StringBuffer sb=new StringBuffer("");
		
		for (int i = 0; i < array.length; i++) {
			Object obj = array[i];
			if(obj==null) {
				continue;
			}
			if(!"".equals(sb.toString())){
				sb.append(",");
			}
			if(obj instanceof String){
				sb.append("'"+obj+"'");
			}else{
				sb.append(obj);
			}
		}
		
		if("".equals(sb.toString())){
			return this;
		}
		
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		
		whereCondition.append(alias+"."+property+" in ("+sb.toString().replace("'", "''")+")");
		
		return this;
	}
	
	/**
	 * IN 查询 
	 * @param property
	 * @param valueStr 逗号隔开的in值
	 * @return
	 */
	public HQLHelper addIn(String property,String valueStr){
		if(StringUtils.isEmpty(valueStr)){
			return this;
		}
		
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		whereCondition.append(alias+"."+property+" in ("+valueStr+")");
		
		return this;
	}
	
	/**
	 * is null查询
	 * @param property
	 * @return
	 */
	public HQLHelper addIsNull(String property){
		if(StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		
		whereCondition.append(alias+"."+property+" is null ");
		return this;
	}
	
	/**
	 * is not null查询
	 * @param property
	 * @return
	 */
	public HQLHelper addIsNotNull(String property){
		if(StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		
		whereCondition.append(alias+"."+property+" is not null ");
		return this;
	}
	
	/**
	 * 小于 查询
	 * @param property
	 * @return
	 */
	public HQLHelper addLessThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(((String)value).isEmpty())
			return this;
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" < '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" < "+value);
		}
		return this;
	}
	
	/**
	 * 小于等于 查询
	 * @param property
	 * @return
	 */
	public HQLHelper addLessEqualThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(((String)value).isEmpty())
			return this;
			
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" <= '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" <= "+value);
		}
		return this;
	}
	
	/**
	 * 大于 查询
	 * @param property
	 * @return
	 */
	public HQLHelper addGreatThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(((String)value).isEmpty())
			return this;
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" > '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" > "+value);
		}
		return this;
	}
	
	/**
	 * 大于等于 查询
	 * @param property
	 * @return
	 */
	public HQLHelper addGreatEqualThan(String property,Object value){
		if(value==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(((String)value).isEmpty())
			return this;
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(value instanceof String){
			whereCondition.append(alias+"."+property+" >= '"+value.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" >= "+value);
		}
		return this;
	}
	
	/**
	 * Between查询
	 * @param property
	 * @return
	 */
	public HQLHelper addBetween(String property,Object valueFrom,Object valueEnd){
		if(valueEnd==null||valueFrom==null||StringUtils.isEmpty(property)){
			return this;
		}
		if(!"".equals(whereCondition.toString())){
			whereCondition.append(" and ");
		}
		if(valueFrom instanceof String ||valueEnd instanceof String  ){
			whereCondition.append(alias+"."+property+" >= '"+valueFrom.toString().replace("'", "''")+"' and "+alias+"."+property+" <= '"+valueEnd.toString().replace("'", "''")+"'");
		}else{
			whereCondition.append(alias+"."+property+" >= "+valueFrom+" and "+alias+"."+property+" <="+valueEnd);
		}
		return this;
	}
	
	
	
	
	/**
	 * 生成HQL查询语句
	 * @return
	 */
	public String getHQL(){
		String hql=" from "+this.entityName+" "+this.alias;
		if(!"".equals(whereCondition.toString())){
			hql+=" where "+whereCondition.toString();
		}
		return hql;
	}
	
	/**
	 * 得到oracle日期sql条件格式
	 * @param date
	 * @return
	 */
	public String oracleToDate(Date date)
	{
		String text="to_date('"+DateUtils.formatDate(date, "yyyy-MM-dd HH:mm:ss")+"','yyyy-MM-dd hh24:mi:ss')";
		return text;
	}
	
	/**
	 * 得到oracle日期sql条件格式
	 * @param date
	 * @return
	 */
	public String oracleToDate(String datestr)
	{
		String text="to_date('"+datestr+"','yyyy-MM-dd hh24:mi:ss')";
		return text;
	}
}
